# @Time : 2021/9/19 21:21
# @Author : lsy
# @Contact: 363925670@qq.com

import pymysql
from himed_dao.UserInfo import UserInfo
from himed_dao.DataInfo import DataInfo


# 数据库接口 himed Data Access Object
class HimedDao:

    # 构造时传入数据库连接信息
    def __init__(self, host='39.105.179.104', port=7306, user='himed', password='xq3kJXN11dC7X34t#ME', db='himed'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db_name = db
        self.db = 0

    # 析构时保证数据库断开
    def __del__(self):
        self.disconnect()

    # 安全化字符串
    def escape(self, info):
        if isinstance(info, UserInfo):
            info.user_name = self.db.escape(info.user_name)
            info.password_sha1 = self.db.escape(info.password_sha1)
            info.home_dir = self.db.escape(info.home_dir)
            info.email = self.db.escape(info.email)
            info.phone = self.db.escape(info.phone)
            info.group_name = self.db.escape(info.group_name)

        if isinstance(info, DataInfo):
            info.file_name = self.db.escape(info.file_name)
            info.extension = self.db.escape(info.extension)
            info.type = self.db.escape(info.type)

        return info

    # 连接数据库
    def connect(self):
        try:
            self.db = pymysql.connect(
                host=self.host, port=self.port, user=self.user, password=self.password, db=self.db_name)
            self.cursor = self.db.cursor()
            return True
        except Exception as e:
            print(e)
            return False

    # 断开数据库
    def disconnect(self):
        try:
            self.db.close()
            return True
        except:
            return False

    # 用户登录
    # 返回 用户对象 和 是否成功
    def login(self, user_name, password_sha1):
        user_name = self.db.escape(user_name)
        password_sha1 = self.db.escape(password_sha1)
        sql = "SELECT * FROM user WHERE user_name={0} AND password_sha1={1}".format(
            str(user_name), str(password_sha1))
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                select_result = cursor.fetchone()

                if len(select_result) > 0:
                    user = UserInfo()
                    user.user_id = select_result[0]
                    user.user_name = select_result[1]
                    user.password_sha1 = select_result[2]
                    user.priv = select_result[3]
                    user.home_dir = select_result[4]
                    user.email = select_result[5]
                    user.phone = select_result[6]
                    user.group_name = select_result[7]

                    return user, True
                else:
                    return UserInfo(), False
        except Exception as e:
            print(e)
            return UserInfo(), False

    # 添加用户
    def user_add(self, user_info):
        if not isinstance(user_info, UserInfo):
            print("添加用户时，user_info 必须是 UserInfo 对象。")
            return False

        if self.is_user_exist(user_info):
            print("要添加的用户名{0}已存在。".format(user_info.user_name))
            return False

        user_info = self.escape(user_info)
        sql = "INSERT INTO user (user_name, password_sha1, priv, home_dir, email, phone, group_name) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})".format(
            user_info.user_name, user_info.password_sha1, user_info.priv, user_info.home_dir, user_info.email,
            user_info.phone, user_info.group_name)

        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                self.db.commit()
                return True

        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    # 修改用户信息
    def user_update(self, user_info):
        if not isinstance(user_info, UserInfo):
            print("修改用户时，user_info 必须是 UserInfo 对象。")
            return False

        user_info = self.escape(user_info)
        sql = "UPDATE user SET user_name = {0}, password_sha1 = {1}, priv = {2}, home_dir = {3}, email = {4}, phone = {5}, group_name = {6} WHERE user_id = {7}".format(
            user_info.user_name, user_info.password_sha1, user_info.priv, user_info.home_dir, user_info.email,
            user_info.phone, user_info.group_name, user_info.user_id)
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                self.db.commit()
                return True

        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    # 删除用户
    def user_del(self, user_id):
        if isinstance(user_id, UserInfo):
            user_id = user_id.user_id

        if not isinstance(user_id, int):
            print("删除用户时，user_id 必须是int或UserInfo类型。")
            return False

        sql = "DELETE FROM user WHERE user_id = {0}".format(user_id)

        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                self.db.commit()
                return True

        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    # 用户是否存在
    def is_user_exist(self, user_name):
        if isinstance(user_name, UserInfo):
            user_name = user_name.user_name

        user_name = self.db.escape(user_name)
        sql = "SELECT COUNT(user_id) FROM user WHERE user_name={0}".format(user_name)
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                select_result = cursor.fetchone()

                if select_result[0]==0:
                    return False
                else:
                    return True
        except Exception as e:
            print(e)
            return False

    # 获取用户信息
    # 返回 用户对象 和 是否成功
    def user_info_refresh(self, user_info):
        if isinstance(user_info, UserInfo):
            user_id = user_info.user_id

        user_id = self.db.escape(user_id)
        sql = "SELECT * FROM user WHERE user_id={0}".format(user_id)
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                select_result = cursor.fetchone()

                if len(select_result) > 0:
                    user = UserInfo()
                    user.user_id = select_result[0]
                    user.user_name = select_result[1]
                    user.password_sha1 = select_result[2]
                    user.priv = select_result[3]
                    user.home_dir = select_result[4]
                    user.email = select_result[5]
                    user.phone = select_result[6]
                    user.group_name = select_result[7]

                    return user, True
                else:
                    return UserInfo(), False
        except Exception as e:
            print(e)
            return UserInfo(), False

    # 添加数据
    def data_add(self, user_info, data_info):
        if not isinstance(user_info, UserInfo):
            print("添加数据时，user_info 必须是 UserInfo 对象。")
            return False
        if not isinstance(data_info, DataInfo):
            print("添加数据时，data_info 必须是 DataInfo 对象。")
            return False

        user_info = self.escape(user_info)
        data_info = self.escape(data_info)

        sql = "INSERT INTO data (user_id, file_name, extension, type, size, date_time) VALUES ({0}, {1}, {2}, {3}, {4}, {5})".format(
            user_info.user_id, data_info.file_name, data_info.extension, data_info.type, data_info.size, data_info.date_time)

        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                self.db.commit()
                return True

        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    # 通过user_id查询用户上传的数据
    # 返回 数据对象的列表 和 是否成功
    def search_data_by_user(self, user_info):
        if isinstance(user_info, UserInfo):
            user_info = user_info.user_id

        if not isinstance(user_info, int):
            print("删除用户时，user_info 必须是int或UserInfo类型。")
            return False

        sql = "SELECT * FROM data WHERE user_id={0}".format(user_info)
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                select_result = cursor.fetchall()
                res_list = []
                if len(select_result) > 0:
                    for i in select_result:
                        data = DataInfo()
                        data.data_id = i[0]
                        data.user_id = i[1]
                        data.file_name = i[2]
                        data.extension = i[3]
                        data.type = i[4]
                        data.size = i[5]
                        data.date_time = i[6]
                        res_list.append(data)

                    return res_list, True
                else:
                    return [], False
        except Exception as e:
            print(e)
            return [], False

